Imports System.Data
Imports System.Data.Common
Imports System.Collections.Generic
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.SqlClient
Imports Seuss.EnterpriseLibrary.DataAccess
Imports Microsoft.VisualBasic.Constants
Imports System.IO
Imports System.Configuration

Public Class DAL_SeussQuery
    Implements IDisposable

#Region "Dispose"

    Private disposedValue As Boolean = False
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
            End If
        End If
        Me.disposedValue = True
    End Sub

    Public Sub Dispose() Implements IDisposable.Dispose
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub

#End Region

#Region "Propiedades"

    Private Db As Database
    ReadOnly Property DataBase() As Database
        Get
            Return Db
        End Get
    End Property
#End Region

#Region "Contructores"

    Public Sub New()
        Db = DatabaseFactory.CreateDatabase
    End Sub

    Public Sub New(ByVal NewDb As Database)
        Db = NewDb
    End Sub

#End Region

#Region "Funciones"

    Public Function Get_Info_Alumno(ByVal cPerCodigo As String, ByVal UsuarioBook As String) As SqlCommand
        Dim Cmd As DbCommand = Db.GetStoredProcCommand("sp_Book_Get_DatosPersonales_Get_DatosPersonales")
        Db.AddInParameter(Cmd, "cPerCodigo", System.Data.DbType.String, cPerCodigo)
        Db.AddInParameter(Cmd, "UsuarioBook", System.Data.DbType.String, UsuarioBook)
        Return Cmd
    End Function

    Public Function Get_FotoPerfil_By_cPerCodigo(ByVal cPerCodigo As String) As SqlCommand
        Try
            Dim vsql As String
            vsql = " SELECT isnull((select 'http://www.ucvchimbote.edu.pe/CompartidoBook/FotosPersonales/'+ cPerCodigo +'/'+ cdoclinurl from perlink where cpercodigo=per.cPerCodigo and ndoclinnum=1 and ndoclintipo=666),'http://www.ucvchimbote.edu.pe/CompartidoBook/FotosPersonales/nothing.jpg') as Foto " & _
                    " FROM Persona per WITH(nolock)  WHERE per.cPerCodigo = '" & cPerCodigo.ToString & "' "

            Using Cmd As SqlCommand = Db.GetSqlStringCommand(vsql.ToString)
                Return Cmd
            End Using
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Get_SeguridadDocumento_By_cPerCodigo(ByVal cPerCodigo As String, ByVal cDocCodigo As String, ByVal nDocTipo As Integer) As SqlCommand
        Try
            Dim vsql As New StringBuilder
            vsql.Append(" select d.cDocCodigo from Documentos d ")
            vsql.Append(" inner join DocPersona dp on d.cDocCodigo =dp.cDocCodigo and dp.nDocPerTipo =1 ")
            vsql.Append(" where d.nDocTipo =" + nDocTipo.ToString + " and d.cDocCodigo ='" + cDocCodigo.ToString + "' and dp.cPerCodigo='" + cPerCodigo.ToString + "' ")

            Using Cmd As SqlCommand = Db.GetSqlStringCommand(vsql.ToString)
                Return Cmd
            End Using
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Get_Foto_Persona(ByVal cPerCodigo As String) As SqlCommand
        Try
            Dim vsql As String
            vsql = " SELECT isnull((select 'http://www.ucvchimbote.edu.pe/CompartidoBook/FotosPersonales/'+ cPerCodigo +'/'+ cdoclinurl from perlink where cpercodigo=per.cPerCodigo and ndoclinnum=1 and ndoclintipo=666),'http://www.ucvchimbote.edu.pe/CompartidoBook/FotosPersonales/nothing.jpg') as Foto " & _
                    " FROM Persona per WITH(nolock)  WHERE per.cPerCodigo = '" & cPerCodigo.ToString & "' "

            Using Cmd As SqlCommand = Db.GetSqlStringCommand(vsql.ToString)
                Return Cmd
            End Using
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Get_estilo_Alumno(ByVal cPerCodigo As String) As SqlCommand
        Try
            Using Cmd As SqlCommand = Db.GetStoredProcCommand("SpWeb_Get_Estilo_Alumno")
                Db.AddInParameter(Cmd, "cPerCodigo", DbType.String, cPerCodigo)
                Return Cmd
            End Using
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Get_UsuariosChat_By_cPerCodigo(ByVal cPerCodigo As String) As SqlCommand
        Try
            Using Cmd As SqlCommand = Db.GetStoredProcCommand("sp_Book_Chat_Get_Usuarios_By_cPerCodigo")
                Db.AddInParameter(Cmd, "cPerCodigo", DbType.String, cPerCodigo)
                Return Cmd
            End Using
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Get_MensajesUsuariosChat_By_cPerCodigo(ByVal cPerCodigo As String) As SqlCommand
        Try
            Using Cmd As SqlCommand = Db.GetStoredProcCommand("sp_Book_Chat_GetMensajes_By_cPercodigo")
                Db.AddInParameter(Cmd, "cPerCodigo", DbType.String, cPerCodigo)
                Return Cmd
            End Using
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Get_menu_Alumno(ByVal cPerCodigo As String) As SqlCommand
        Try
            Using Cmd As SqlCommand = Db.GetStoredProcCommand("spweb_get_menualumno")
                Return Cmd
            End Using
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
    Public Function UpdIns_Estilo_Alumno(ByVal cPerCodigo As String, ByVal nObjTipo As Integer) As SqlCommand
        Try
            Using Cmd As SqlCommand = Db.GetStoredProcCommand("SpWeb_UdpIns_EstiloAlumno")
                Db.AddInParameter(Cmd, "cPerCodigo", DbType.String, cPerCodigo)
                Db.AddInParameter(Cmd, "nObjTipo", DbType.Int32, nObjTipo)
                Return Cmd
            End Using
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
    Public Function Get_Permiso_By_User(ByVal cPerCodigo As String, _
                                           ByVal cIntJerarquia As String, _
                                           ByVal nSisGruCodigo As Long, _
                                           ByVal nSisGruTipo As Long, _
                                           Optional ByVal nObjTipo As Long = 1011, _
                                           Optional ByVal nIntTipo As Long = 0) As SqlCommand
        Try
            Dim Cmd As SqlCommand = Db.GetStoredProcCommand("sp_Permiso_Get_By_User")
            Db.AddInParameter(Cmd, "cPerCodigo", System.Data.DbType.String, cPerCodigo)
            Db.AddInParameter(Cmd, "cIntJerarquia", System.Data.DbType.String, cIntJerarquia)
            Db.AddInParameter(Cmd, "nSisGruCodigo", System.Data.DbType.Int32, nSisGruCodigo)
            Db.AddInParameter(Cmd, "nSisGruTipo", System.Data.DbType.Int32, nSisGruTipo)
            Db.AddInParameter(Cmd, "nObjTipo", System.Data.DbType.Int32, nObjTipo)
            Db.AddInParameter(Cmd, "nIntTipo", System.Data.DbType.Int32, nIntTipo)
            Return Cmd
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function Get_Marcaciones_BY_Persona_Fechas(ByVal cPerJuridica As String, ByVal cpercodigo As String, ByVal Fechaini As String, ByVal fechafin As String) As SqlCommand
        Try
            Dim cmd As SqlCommand = Db.GetStoredProcCommand("sp_Horario_Get_Asistencia_Persona")
            Db.AddInParameter(cmd, "cPerJurCodigo", System.Data.DbType.String, cPerJuridica)
            Db.AddInParameter(cmd, "cPerCodigo", System.Data.DbType.String, cpercodigo)
            Db.AddInParameter(cmd, "dfechaIni", System.Data.DbType.String, Fechaini)
            Db.AddInParameter(cmd, "dfechaFin", System.Data.DbType.String, fechafin)

            Return cmd
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function Get_Marcaciones_Sin_Procesar(ByVal cperjurcodigo As String, ByVal cpercodigo As String, Optional ByVal fechaini As String = "", Optional ByVal fechafin As String = "") As SqlCommand
        Dim vsql As New StringBuilder

        vsql.Append(" SET DATEFORMAT dmy ")
        vsql.Append(" DECLARE @fechainicio char(8) ")
        vsql.Append(" DECLARE @fechafin  char(8)  ")
        vsql.Append(" set @fechainicio='" + fechaini.ToString + "' ")
        vsql.Append(" set @fechafin='" + fechafin.ToString + "' ")
        vsql.Append(" SELECT CONVERT(NCHAR(10),dtdatetime,103) as fecha,CONVERT(NCHAR(8),dtdatetime,108) as hora ")
        vsql.Append(" FROM tb_reportslist T ")
        vsql.Append(" INNER JOIN PerIdentifica PI ON T.nUserID =PI.cPerIdeNumero  and pi.nPerIdeTipo =25 ")
        vsql.Append(" inner join Persona p on pi.cPerCodigo =p.cPerCodigo  ")
        vsql.Append(" WHERE PI.cPerCodigo ='" + cpercodigo.ToString + "' and t.cperjurcodigo='" + cperjurcodigo.ToString + "'  and")
        vsql.Append(" t.dtDateTime >=CONVERT(DATETIME, @fechainicio + ' 00:00:00:00', 101) and t.dtDateTime <=CONVERT(DATETIME, @fechafin  + ' 23:59:59:59', 101) order by CONVERT(NCHAR(10),dtdatetime,103),CONVERT(NCHAR(8),dtdatetime,108)")

        Try
            Dim Cmd As SqlCommand = Db.GetSqlStringCommand(vsql.ToString)

            Return Cmd
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function

    Public Function Sp_Get_UnidadOrganizacional() As SqlCommand
        Try
            Dim Cmd As SqlCommand = Db.GetStoredProcCommand("Sp_Get_UnidadOrganizacional")

            Return Cmd
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    Public Function Sp_Get_VerPerido() As SqlCommand
        Try
            Dim Cmd As SqlCommand = Db.GetStoredProcCommand("Sp_Get_PeriodoActual")

            Return Cmd
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    Public Function sp_UO_Get_Unidad_By_cPerjuridica(ByVal cperjuridica As String) As SqlCommand
        Try
            Dim Cmd As SqlCommand = Db.GetStoredProcCommand("sp_UO_Get_Unidad_By_cPerjuridica")
            Db.AddInParameter(Cmd, "cperjuridica", System.Data.DbType.String, cperjuridica)
            Return Cmd
        Catch ex As Exception
            Throw ex
        End Try

    End Function
    Public Function sp_Documentos_Get_MuestraNoticias() As SqlCommand
        Try
            Dim Cmd As SqlCommand = Db.GetStoredProcCommand("sp_Documentos_Get_MuestraNoticias")

            Return Cmd
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    Public Function sp_Constante_Get_Tipo_By_nconcodigo(ByVal NCONCODIGO As Integer, ByVal nconvalor As Long) As SqlCommand
        Try
            Dim Cmd As SqlCommand = Db.GetStoredProcCommand("sp_Constante_Get_By_nconcodigo_nconvalor")
            Db.AddInParameter(Cmd, "nconcodigo", System.Data.DbType.Int32, NCONCODIGO)
            Db.AddInParameter(Cmd, "nconvalor", System.Data.DbType.Int32, nconvalor)
            Return Cmd
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    Public Function sp_Documentos_Upd_By_cdoccodigo(ByVal cdoccodigo As String, ByVal nconvalor As Integer) As SqlCommand
        Try
            Dim Cmd As SqlCommand = Db.GetStoredProcCommand("sp_Documento_Upd_Estado_By_cDocCodigo")
            Db.AddInParameter(Cmd, "cdoccodigo", System.Data.DbType.String, cdoccodigo)
            Db.AddInParameter(Cmd, "nEstado", System.Data.DbType.Int32, nconvalor)
            Return Cmd
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
    Public Function sp_Constante_Get_By_nconcodigo(ByVal NCONCODIGO As Integer, ByVal nconvalor1 As Integer, ByVal nconvalor2 As Integer) As SqlCommand
        Try
            Dim Cmd As SqlCommand = Db.GetStoredProcCommand("sp_Constante_Get_By_nconcodigo")
            Db.AddInParameter(Cmd, "nconcodigo", System.Data.DbType.Int32, NCONCODIGO)
            Db.AddInParameter(Cmd, "nconvalor1", System.Data.DbType.Int32, nconvalor1)
            Db.AddInParameter(Cmd, "nconvalor2", System.Data.DbType.Int32, nconvalor2)

            Return Cmd
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function

    Public Function sp_Documentos_Get_MuestraNoticias_By_Estado(ByVal ndoctipo As Long, ByVal nestado As Integer, Optional ByVal nUniOrgCodigo As Long = 0) As SqlCommand
        Try
            Dim Cmd As SqlCommand = Db.GetStoredProcCommand("sp_Documento_Get_Noticia_By_Estado")
            Db.AddInParameter(Cmd, "ndoctipo", System.Data.DbType.Int32, ndoctipo)
            Db.AddInParameter(Cmd, "ndocestado", System.Data.DbType.Int32, nestado)
            If nUniOrgCodigo <> 0 Or nUniOrgCodigo <> -1 Then Db.AddInParameter(Cmd, "nUniOrgCodigo", System.Data.DbType.Int32, nUniOrgCodigo)
            Return Cmd
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function

    Public Function sp_Documentos_Get_MuestraFiliales_By_Tipo(ByVal cPercodigo As String, ByVal nObjCodig As String) As SqlCommand
        Try
            Dim Cmd As SqlCommand = Db.GetStoredProcCommand("usp_MiSeuss_Get_Fial_by_nIntCodigo")
            Db.AddInParameter(Cmd, "nObjCodigo", System.Data.DbType.String, nObjCodig)
            Db.AddInParameter(Cmd, "cPerCodigo", System.Data.DbType.String, cPercodigo)
            Return Cmd
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function



    Public Function sp_Constante_Get_TipoNoticias(ByVal CodigoPersona As String) As SqlCommand
        Try
            'Dim Cmd As SqlCommand = Db.GetSqlStringCommand("SELECT nConCodigo, nConvalor, cConDescripcion FROM Constante WHERE nConCodigo=1063 AND nConValor IN(84010208,84010209)")
            Dim Cmd As SqlCommand = Db.GetStoredProcCommand("usp_MiSeuss_Get_Tipo_by_cPercodigo")
            Db.AddInParameter(Cmd, "cPerCodigo", System.Data.DbType.String, CodigoPersona)
            Return Cmd
        Catch ex As Exception
            Throw ex
        End Try
        Return Nothing
    End Function
#End Region

End Class
